from dao.iotdbhelper import Iot
from datetime import datetime
import pandas as pd
import time

from utils.logger import Logger

logger = Logger('Iot').getlogger()
# 废水泵 b3Qm59mg9  ["K_JZFSBn3",
#               "K_JZFSBs3",
#               "K_JZFSBs1",
#               "K_JZFSBs2",
#               "K_JZFSBn2",
#               "K_JZFSBn1",
#               "K_DAFSBd3",
#               "K_DAFSBd2",
#               "K_DAFSBd1",
#               "K_XAFSBx3",
#               "K_XAFSBx2",
#               "K_XAFSBx1"
#               ]
iot = Iot()
device_ids = ["K_JZFSBn3",
              "K_JZFSBs3",
              "K_JZFSBs1",
              "K_JZFSBs2",
              "K_JZFSBn2",
              "K_JZFSBn1",
                "K_DAFSBd3",
                "K_DAFSBd2",
                "K_DAFSBd1",
                "K_XAFSBx3",
                "K_XAFSBx2",
                "K_XAFSBx1"
              ]  # 多个设备ID列表
time_range_start = '2025-01-02 00:00:00'  # 你希望获取的时间范围开始时间
time_range_end = '2025-02-18 23:59:59'    # 你希望获取的时间范围结束时间

# 用于存储所有设备事件的列表
all_events = []
# 用于存储所有日志信息的列表
all_logs = []

# 转换时间为时间戳
start_timestamp = int(time.mktime(datetime.strptime(time_range_start, '%Y-%m-%d %H:%M:%S').timetuple()) * 1000)
end_timestamp = int(time.mktime(datetime.strptime(time_range_end, '%Y-%m-%d %H:%M:%S').timetuple()) * 1000)

# 查询每个设备的数据
for device_id in device_ids:
    try:
        # 获取测点数据
        sql_query = f"SELECT Running FROM root.iotdata.b3Qm59mg9.{device_id} WHERE time >= {start_timestamp} AND time <= {end_timestamp} order by time"
        result = iot.search(sql_query)

        if result.empty:
            print(f"设备 {device_id} 在指定时间范围内没有数据。")
            continue

        # 将结果转换为 pandas DataFrame
        df = pd.DataFrame(result)

        # 如果返回的数据中包含时间戳和状态信息，需要先提取
        df['timestamp'] = df['Time'].apply(lambda x: datetime.fromtimestamp(x / 1000))
        df['pump_status'] = df[f'root.iotdata.b3Qm59mg9.{device_id}.Running']

        # 收集开启状态的日志信息
        for index, row in df.iterrows():
            if row['pump_status'] == 1:
                all_logs.append({
                    'device_id': device_id,
                    'timestamp': row['timestamp']
                })

        # 处理开关时间
        open_time = None
        for index, row in df.iterrows():
            if row['pump_status'] == 1 and open_time is None:  # 开始
                open_time = row['timestamp']
            elif row['pump_status'] == 0 and open_time is not None:  # 结束
                close_time = row['timestamp']
                duration = round((close_time - open_time).total_seconds() / 60, 2)  # 计算持续时间（分钟）并保留两位小数
                all_events.append({
                    "设备名": device_id,
                    "开启时间": open_time,
                    "关闭时间": close_time,
                    "持续时间/min": duration
                })
                open_time = None  # 重置 open_time

    except Exception as e:
        print(f"设备 {device_id} 数据处理失败：{e}")

# 对日志信息进行排序并打印
all_logs_df = pd.DataFrame(all_logs)
all_logs_df = all_logs_df.sort_values(by=['device_id', 'timestamp'], ascending=[True, True])

# 打印排序后的日志
for _, log in all_logs_df.iterrows():
    logger.info(f"设备{log['device_id']}在{log['timestamp']}时间状态开启了")

# 将结果按设备名分组，并在每个组内按开启时间排序
events_df = pd.DataFrame(all_events)
events_df = events_df.sort_values(by=['设备名', '开启时间'], ascending=[True, True])

name = f'废水泵开启时间统计1.02-2.18.xlsx'

# 创建 Excel 写入器
with pd.ExcelWriter(name, engine='openpyxl') as writer:
    # 写入总表
    events_df.to_excel(writer, sheet_name='总表', index=False)
    
    # 获取总表 worksheet
    worksheet = writer.sheets['总表']
    
    # 设置列宽和格式
    worksheet.column_dimensions['A'].width = 15  # 设备名
    worksheet.column_dimensions['B'].width = 22  # 开启时间
    worksheet.column_dimensions['C'].width = 22  # 关闭时间
    worksheet.column_dimensions['D'].width = 15  # 持续时间
    
    # 设置时间和数字格式
    for row in range(2, len(events_df) + 2):  # 从第二行开始（跳过标题）
        worksheet.cell(row=row, column=2).number_format = 'yyyy-mm-dd hh:mm:ss'
        worksheet.cell(row=row, column=3).number_format = 'yyyy-mm-dd hh:mm:ss'
        worksheet.cell(row=row, column=4).number_format = '0.00'  # 持续时间显示两位小数

    # 为每个设备创建单独的sheet
    for device_id in device_ids:
        device_data = events_df[events_df['设备名'] == device_id]
        if not device_data.empty:
            # 写入设备sheet
            device_data.to_excel(writer, sheet_name=device_id, index=False)
            
            # 获取设备sheet worksheet
            worksheet = writer.sheets[device_id]
            
            # 设置列宽和格式
            worksheet.column_dimensions['A'].width = 15  # 设备名
            worksheet.column_dimensions['B'].width = 22  # 开启时间
            worksheet.column_dimensions['C'].width = 22  # 关闭时间
            worksheet.column_dimensions['D'].width = 15  # 持续时间
            
            # 设置时间和数字格式
            for row in range(2, len(device_data) + 2):
                worksheet.cell(row=row, column=2).number_format = 'yyyy-mm-dd hh:mm:ss'
                worksheet.cell(row=row, column=3).number_format = 'yyyy-mm-dd hh:mm:ss'
                worksheet.cell(row=row, column=4).number_format = '0.00'  # 持续时间显示两位小数